USE [DSRSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetIndicatorDataTablesByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetIndicatorDataTablesByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetIndicatorDataTablesByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	DECLARE @indicators TABLE
	(
		IndicatorId uniqueidentifier not null,
		CompanyId uniqueidentifier not null,
		IndicatorTemplateId uniqueidentifier not null,
		IndicatorNumber nvarchar(max) null,
		AnswerNumber int not null
	)
	INSERT INTO @indicators
	SELECT	Indicators.IndicatorId, 
	        Companies.CompanyId, 
			IndicatorTemplates.IndicatorId AS IndicatorTemplateId,
			IndicatorTemplates.Number AS IndicatorNumber,
			AnswerCategories.Number AS AnswerNumber
	FROM	dbo.Companies AS Companies 
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = Companies.CompanyId
	INNER JOIN dbo.IndicatorsInCompanies AS IndicatorsInCompanies ON Companies.CompanyId = IndicatorsInCompanies.CompanyId 
	INNER JOIN dbo.Indicators AS Indicators ON IndicatorsInCompanies.IndicatorId = Indicators.IndicatorId AND Companies.WorkingVersion - 1 = Indicators.Version 
	INNER JOIN dbo.Indicators AS IndicatorTemplates ON Indicators.Number = IndicatorTemplates.Number 
	INNER JOIN dbo.AnswerCategories AS AnswerCategories ON Indicators.RatedSelectedAnswer = AnswerCategories.AnswerCategoryId
	WHERE	(Indicators.Enabled = 1) 
	AND		(IndicatorTemplates.Template = 1)  
	AND		(Indicators.Template = 0) 
	AND 	(Companies.WorkingVersion > 1002)  
	AND		(Companies.IsParked = 0)  
	AND		(Indicators.Number = N'E.1.9' OR Indicators.Number = N'E.1.10')
		           
	DECLARE @table TABLE
	(
		Name nvarchar(max) null, 
		Year int null, 
		Value nvarchar(max) null
	)
		
	DECLARE @indicatorTables TABLE
	(
		IndicatorId uniqueidentifier not null,
		CompanyId uniqueidentifier not null,
		IndicatorTemplateId uniqueidentifier not null,
		IndicatorNumber nvarchar(max) null,
		IsDataAvailable bit not null,
		IsInsufficientDisclosure bit not null,
		Name nvarchar(max) null,  
		Year int null, 
		Value nvarchar(max) null
	)
		
	declare @indicatorId uniqueidentifier
	declare @companyId uniqueidentifier
	declare @indicatorTemplateId uniqueidentifier
	declare @indicatorNumber nvarchar(max)
	declare @answerNumber int
	declare @isDataAvailable bit
	declare @isInsufficientDisclosure bit
		
	WHILE (SELECT Count(*) FROM @indicators) > 0 
		BEGIN 
				
			SELECT TOP 1 
				@indicatorId = IndicatorId,
				@companyId = CompanyId,
				@indicatorTemplateId = IndicatorTemplateId,
				@indicatorNumber = IndicatorNumber,
				@answerNumber = AnswerNumber
			FROM @indicators
				
			-- 7 = Data for this company is not yet available
			SET @isDataAvailable = 1

			IF @answerNumber = 7
				BEGIN
					SET @isDataAvailable = 0
				END
				
			-- for E.1.9 6 = Disclosure on carbon emissions is insufficient to determine performance relative to peers
			-- for E.1.10 6 = Disclosure is insufficient to calculate the company's carbon intensity trend over the last 3-5 years
			SET @isInsufficientDisclosure = 0
				
			IF @answerNumber = 6
				BEGIN
					SET @isInsufficientDisclosure = 1
				END
				
   		    INSERT INTO @table EXEC dbo.GetIndicatorDataTable @IndicatorId


			IF (SELECT Count(*) FROM @table) > 0
				BEGIN
					INSERT INTO @indicatorTables
					SELECT @indicatorId AS IndicatorId,
					       @companyId AS CompanyId,
						   @indicatorTemplateId AS IndicatorTemplateId,
						   
						   @indicatorNumber AS IndicatorNumber,
						   @isDataAvailable AS IsDataAvailable,
						   @isInsufficientDisclosure AS IsInsufficientDisclosure,
						   Name, 
						   Year, 
						   Value
					FROM  @table
				END
			ELSE
				BEGIN
					INSERT INTO @indicatorTables
					VALUES
					(
						@indicatorId,
						@companyId,
						@indicatorTemplateId,
						@indicatorNumber,
						@isDataAvailable,
						@isInsufficientDisclosure,
						null, 
						null, 
						null
					)
				END

			DELETE FROM @table
				
			DELETE @indicators WHERE IndicatorId = @indicatorId
		END
		
	SELECT	IndicatorId,
			CompanyId, 
			IndicatorTemplateId, 
			IndicatorNumber,
			IsDataAvailable,
			IsInsufficientDisclosure,
			Name, 
			Year, 
			Value  
	FROM @indicatorTables
END
GO


